create database ResultDeclarationBoard
go

use ResultDeclarationBoard
go

create table Curriculums
(
	cuId int identity(1,1) primary key,
	cuName varchar(50)
)
go

insert into Curriculums(cuName) values('Curriculums A')
insert into Curriculums(cuName) values('Curriculums B')
insert into Curriculums(cuName) values('Curriculums C')
go

create table Subjects
(
	suId int identity(1,1) primary key,
	suName varchar(50),
	suCurri int foreign key references Curriculums(cuId),
	suSemester int,
	suReEval float,
	suReCount float
)
go

insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('HDJ', 1, 1, 50, 25)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('JAVA', 1, 1, 50, 25)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('RDBMS', 1, 1, 30, 15)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('SQL_SDO', 2, 2, 40, 20)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('ADVJ', 2, 2, 25, 10)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('C#', 2, 2, 30, 20)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('ASP.NET', 3, 3, 50, 30)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('IXJ', 3, 4, 50, 20)
insert into Subjects(suName, suCurri, suSemester, suReEval, suReCount) values('DEAE', 3, 4, 40, 25)
go

create table Classes
(
	claId int identity(1,1) primary key,
	claName varchar(50),
	claSubject int foreign key references Subjects(suId),
	claBranch varchar(10)
)
go

insert into Classes(claName, claSubject, claBranch) values('Class 1-A', 1, 'Branch A')
insert into Classes(claName, claSubject, claBranch) values('Class 2-A', 4, 'Branch A')
insert into Classes(claName, claSubject, claBranch) values('Class 3-A', 7, 'Branch A')
insert into Classes(claName, claSubject, claBranch) values('Class 1-B', 2, 'Branch B')
insert into Classes(claName, claSubject, claBranch) values('Class 2-B', 5, 'Branch B')
insert into Classes(claName, claSubject, claBranch) values('Class 3-B', 8, 'Branch B')
insert into Classes(claName, claSubject, claBranch) values('Class 1-C', 3, 'Branch C')
insert into Classes(claName, claSubject, claBranch) values('Class 2-C', 6, 'Branch C')
insert into Classes(claName, claSubject, claBranch) values('Class 3-C', 9, 'Branch C')
go

create table Students
(
	stuId int identity(1,1) primary key,
	stuClass int foreign key references Classes(claId),
	stuName varchar(50),
	stuGender varchar(10),
	stuAddress varchar(100),
	stuPhone varchar(20),
	stuEmail varchar(50)
)
go

insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(1, 'Lelouch', 'Male', 'Tokyo', '492394', 'Lulu@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(1, 'Suzaku', 'Male', 'Area 9', '893843', 'Suzaku@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(1, 'Sherry', 'Female', 'England', '43423', 'Sherry@yahoo.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(2, 'Zeus', 'Male', 'Olympia', '23909', 'Zeus@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(2, 'Athena', 'Female', 'Olymmpia', '55435', 'Athena@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(2, 'Poseidon', 'Male', 'Ocean', '9058490', 'Poseidon@yahoo.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(3, 'Serge', 'Male', 'Arni', '8930285', 'Silent@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(3, 'Harle', 'Female', 'Fate', '59438594', 'Paradox@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(3, 'Kid', 'Female', 'Orphan', '543543', 'Traveler@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(4, 'Haseo', 'Male', 'Mac Anu', '76865', 'Skeith@deathmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(4, 'Kite', 'Male', 'Aura', '47654', 'hero@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(4, 'Tokio', 'Male', 'Dolphin', '65857', 'copy@yahoo.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(5, 'Sephiroth', 'Male', 'Lifestream', '57856865', 'angel@godmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(5, 'Cloud', 'Male', 'Church', '239329', 'emo@yahoo.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(5, 'Tifa', 'Female', '7th Heaven', '3832902', 'Tifa@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(6, 'Wayne', 'Male', 'Bat Cave', '64363453', 'dark@hero.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(6, 'Flash', 'Male', 'unknown', '325234', 'ligh@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(6, 'Parker', 'Male', 'USA', '94849', 'spider@glue.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(7, 'Rantaro', 'Male', 'Ninja Academy', '9458495', 'Rantaro@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(7, 'Kirimaru', 'Male', 'War', '84938439', 'Coin@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(7, 'Shinbe', 'Male', 'Mansion', '45454', 'Foodlover@yahoo.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(8, 'Sagara', 'Male', 'Mythril', '4532242', 'urzu7@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(8, 'Chidori', 'Female', 'Tokyo', '404890238', 'Whisper@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(8, 'Weber', 'Male', 'Mythril', '49324892', 'Sniper@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(9, 'Araragi', 'Male', 'Tokyo', '3324903', 'Half@gmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(9, 'Senjougahara', 'Female', 'Abandon', '48294892', 'Crab@hotmail.com')
insert into Students(stuClass, stuName, stuGender, stuAddress, stuPhone, stuEmail) values(9, 'Hanekawa', 'Female', 'Street', '930930', 'Neko@hotmail.com')
go

create table Marks
(
	maId int identity(1,1) primary key,
	maStudent int foreign key references Students(stuId),
	maSubject int foreign key references Subjects(suId),
	maDate datetime,
	maMark float
)
go

insert into Marks(maStudent, maSubject, maDate, maMark) values(1, 1, '1/1/2011', 5)
insert into Marks(maStudent, maSubject, maDate, maMark) values(2, 1, '1/1/2011', 15)
insert into Marks(maStudent, maSubject, maDate, maMark) values(3, 1, '1/1/2011', 20)
insert into Marks(maStudent, maSubject, maDate, maMark) values(4, 4, '2/3/2011', 9)
insert into Marks(maStudent, maSubject, maDate, maMark) values(5, 4, '2/3/2011', 8)
insert into Marks(maStudent, maSubject, maDate, maMark) values(6, 4, '2/3/2011', 16)
insert into Marks(maStudent, maSubject, maDate, maMark) values(7, 7, '3/8/2011', 11)
insert into Marks(maStudent, maSubject, maDate, maMark) values(8, 7, '3/8/2011', 14)
insert into Marks(maStudent, maSubject, maDate, maMark) values(9, 7, '3/8/2011', 16)
insert into Marks(maStudent, maSubject, maDate, maMark) values(10, 2, '4/15/2011', 17)
insert into Marks(maStudent, maSubject, maDate, maMark) values(11, 2, '4/15/2011', 22)
insert into Marks(maStudent, maSubject, maDate, maMark) values(12, 2, '4/15/2011', 20)
insert into Marks(maStudent, maSubject, maDate, maMark) values(13, 5, '5/16/2011', 14)
insert into Marks(maStudent, maSubject, maDate, maMark) values(14, 5, '5/16/2011', 15)
insert into Marks(maStudent, maSubject, maDate, maMark) values(15, 5, '5/16/2011', 5)
insert into Marks(maStudent, maSubject, maDate, maMark) values(16, 8, '7/27/2011', 12)
insert into Marks(maStudent, maSubject, maDate, maMark) values(17, 8, '7/27/2011', 13)
insert into Marks(maStudent, maSubject, maDate, maMark) values(18, 8, '7/27/2011', 18)
insert into Marks(maStudent, maSubject, maDate, maMark) values(19, 3, '9/3/2011', 6)
insert into Marks(maStudent, maSubject, maDate, maMark) values(20, 3, '9/3/2011', 7)
insert into Marks(maStudent, maSubject, maDate, maMark) values(21, 3, '9/3/2011', 10)
insert into Marks(maStudent, maSubject, maDate, maMark) values(22, 6, '10/21/2011', 12)
insert into Marks(maStudent, maSubject, maDate, maMark) values(23, 6, '10/21/2011', 15)
insert into Marks(maStudent, maSubject, maDate, maMark) values(24, 6, '10/21/2011', 17)
insert into Marks(maStudent, maSubject, maDate, maMark) values(25, 9, '11/20/2011', 23)
insert into Marks(maStudent, maSubject, maDate, maMark) values(26, 9, '11/20/2011', 19)
insert into Marks(maStudent, maSubject, maDate, maMark) values(27, 9, '11/20/2011', 11)

go

create table Schedules
(
	schId int identity(1,1) primary key,
	schSubject int foreign key references Subjects(suId),
	schStart datetime,
	schEnd datetime,
	schDate datetime
)

insert into Schedules(schSubject, schStart, schEnd, schDate) values(1, '1/1/2012', '1/5/2012', '1/7/2012')
insert into Schedules(schSubject, schStart, schEnd, schDate) values(5, '1/7/2012', '1/11/2012', '1/13/2012')
insert into Schedules(schSubject, schStart, schEnd, schDate) values(9, '1/13/2012', '1/17/2012', '1/19/2012')
go

create table Registers
(
	reId int identity(1,1) primary key,
	reStudent int foreign key references Students(stuId),
	reSchedule int foreign key references Schedules(schId),
	reType varchar(15)
)
go

insert into Registers(reStudent, reSchedule, reType) values(1, 1, 'Re-Evaluate')
insert into Registers(reStudent, reSchedule, reType) values(15, 2, 'Re-Evaluate')
insert into Registers(reStudent, reSchedule, reType) values(27, 3, 'Re-Count')
go

create table Users
(
	usId int identity(1,1) primary key,
	usLogin varchar(30),
	usPass varchar(30),
	usName varchar(50),
	usGender varchar(10),
	usAddress varchar(100),
	usPhone varchar(20),
	usEmail varchar(50),
	usBranch varchar(10)
)
go

insert into Users(usLogin, usPass, usName, usGender, usAddress, usPhone, usEmail, usBranch) values('admin', 'admin ', 'The Admin', 'Male', 'Company', '395843', 'admin@godmail.com', 'Branch A')
go

create table Notifications
(
	noId int identity(1,1) primary key,
	noTitle varchar(50),
	noInfo varchar(500),
	noDate datetime
)
go

insert into Notifications(noTitle, noInfo, noDate) values('Schedule for Re-Evaluate and Re-Count', 'Please check the schedule for date of Re-Evaluate and Re-Count', '12/24/2011')
go

create table Feedbacks
(
	feId int identity(1,1) primary key,
	feTitle varchar(50),
	feInfo varchar(500),
	feDate datetime
)
go

insert into Feedbacks(feTitle, feInfo, feDate) values('Internet connection is too slow', 'Please make it faster', '1/4/2012')
go
